Brief Full Advanced Search Search Tips
|  |  | To access the contents, click the chapter and section titles. Visual Basic 6 Programming Blue Book: The Most Complete, Hands-On Resource for Writing Programs with Microsoft Visual Basic 6! (Publisher: The Coriolis Group) Author(s): Peter G. Aitken ISBN: 1576102815 Publication Date: 08/01/98 Search this book:
Previous | Table of Contents | Next
The Recordset object is not only powerful, it is also complex. To complicate matters, you can choose from three types of Recordsets: Table, Dynaset, and Snapshot. Each uses basically the same commands, but they interact differently with their underlying table (called the base table) or tables. The Recordset types provide the following: • A Snapshot type of Recordset provides a static, read-only copy of the base table. The records in the base table cannot be deleted or modified (although new records can be inserted). Changes to the base table—in a multiuser environment, for example, when another user adds data to the table—are not reflected in the Snapshot. Technically speaking, a Snapshot-type Recordset contains the results of an SQL query operation on the base table. In other words, the Snapshot contains selected records from the base table. When attached to a Data control, a Snapshot’s initial condition is to contain all the records from the base table—in other words, the results of a “select all records” query. Snapshot-type Recordsets are intended for situations where your program will be reading but not modifying the table data, such as in report generation. • A Dynaset-type Recordset provides dynamic access to a table’s records. Records can be added, deleted, and edited. Also, changes to the table made by others are reflected in the Dynaset. Otherwise, a Dynaset is very much like a Snapshot type. • A Table-type Recordset provides the most direct access to all of the base table’s records. No SQL query is implicit in a Table type, so it always includes all of the table’s records. This type of Recordset provides quick access to all of the table’s records, but extracting particular sets of records can require some complicated programming. Of course, my descriptions of the three types of Recordsets are rather oversimplified. Other differences exist among them that only come into play in the most complicated and demanding types of database applications, where tables contain hundreds of thousands of records. With a few exceptions, such as the Snapshot-type Recordset’s inability to update records in the base table, the three types have the same capabilities. The main difference among them is in database performance (speed). Further discussion in this area is beyond the scope of an introductory book, but the amount of published information is abundant if you are interested. I will just leave you with this bit of advice: When in doubt, use a Dynaset. TIP: SQL SQL stands for Structured Query Language, an industry-standard database manipulation language originally developed by IBM. You can use SQL for many tasks, such as selecting a subset of records from a table. I will have more to say about SQL in Chapter 20. Finding A Record Finding a particular record in the database table is also simplified by the Recordset object’s methods. Our database program will be limited to searching the Title field, but the same methods apply to searches, or queries, on other fields. While a Recordset method performs the actual search, some additional programming is required to prepare for the search and respond to its result. This step is accomplished in the FindRecord procedure, which is shown in Listing 19.16. Listing 19.16 The FindRecord procedure.
    Public Sub FindRecord()

    Dim Template As String
    Dim Previous As String

    Template = InputBox(“Find what?”, “Find a Title”)

    ‘ If user selects Cancel a blank string is returned.
    If Template = “” Then Exit Sub

    ‘ Save the current position.
    Previous = Data1.Recordset.Bookmark

    ‘ Set up the search template and use the FindFirst
    ‘ method to perform the search.
    Template = “[Title]= ” & Chr$(34) & Template & Chr$(34)
    Data1.Recordset.FindFirst Template

    ‘ If no match, return to original record and
    ‘ display a message.
    If Data1.Recordset.NoMatch Then
        Data1.Recordset.Bookmark = Previous
        MsgBox (“No match found”)
    End If

    End Sub
The procedure first uses the InputBox function to ask the user what to search for. InputBox displays a dialog box with a prompt, a Text Box where the user types a response, and OK and Cancel buttons. If the user selects OK, the string entered in the Text Box is returned by the function. If the user selects Cancel, an empty string is returned, in which case the user exits the procedure. The next step is incorporating the desired search template into a command string that can be passed to the Recordset object’s FindFirst method. This string consists of the name of the field you are querying (in brackets), followed by an equal sign and the string for which you are searching (in single or double quotes). For example, to find records where the Title field contains “Rubber Soul” (the title of a Beatles album, for those of you too young to know), you would use this command string:
    [Title] = “Rubber Soul”
> While either single or double quotes will work to enclose the search string, I prefer using double quotes. This permits the search string itself to contain a single quote, letting us search for other Beatles titles, such as “Hard Day’s Night.” Because you cannot enter a double quote directly into a Basic string, use the Chr$ function to return the double quote character, passing the argument of 34 (the ASCII code for the double quote). You can see how this is accomplished in the listing. The FindRecord procedure uses the Recordset object’s Bookmark property to keep track of the record that is current before the search is performed. This step is necessary: If the FindFirst method finds no match, it makes the first record in the table current. If you want the record that was displayed when the search was initiated to remain displayed should the search fail, you must make a note of it and explicitly return there. The Bookmark property returns a string value that identifies the current record. Understanding the nature of this string is not necessary. Just save it. If the search fails, assign it back to the Recordset’s Bookmark property to make that record current again. How do you know if the search has failed? Because the Recordset object’s NoMatch property is False unless a search has failed, we can determine the search outcome by testing this property. If the search is successful, the matching record automatically becomes current and is displayed in the bound controls. The FindFirst method looks for the first record that exactly matches the search template. The FindLast method finds the last matching record, in effect searching from the end of the table. If you have used FindFirst to locate the first match, you can use the FindNext method one or more times to locate subsequent matching records. TIP: Fine-Tuning The Search What if you don’t want the search restricted to exact matches? For example, if you enter the search string “Hard”, you would like the search to locate titles, such as A Hard Day’s Night and Hard Times. To accomplish this, you must use the Like keyword in the command string you pass to the FindFirst or FindNext methods. You can use the * wildcard to represent any string of zero or more characters. For example, the command string
>     [Title] Like "*Hard*"
will find any record whose Title field contains the word “Hard”. The Like keyword is a SQL keyword, and the Jet database engine understands SQL. In other words, you can pass SQL statements to the FindNext method and to related Recordset object methods. SQL is a complex and powerful database language that I will not attempt to cover in this book. If you want to learn more about SQL, you can look at the Visual Basic Help system or refer to one of the many books published on the topic. Listing All Records As with the earlier address-list database program, we would like the capability to display an alphabetical list of all the records in the database. As before, we’ll use a separate form with a List Box control, adding a Command Button to close the list window. As an added feature, we’ll include code that will display the program’s main form, the entire record for a title when the user clicks on that title in the list.
Previous | Table of Contents | Next

| Products | Contact Us | About Us | Privacy | Ad Info | Home Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement.